﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Data;

namespace ecomerci
{
    class sqlite
    {
        public sqlite()
        {

        }
        public SQLiteConnection startConnection()
        {
            SQLiteConnection sqlConnection = new SQLiteConnection();
            sqlConnection.ConnectionString = "data source=" + Application.StartupPath + @"\ecomerci.db";
            sqlConnection.Open();
            return sqlConnection;
        }

        public void killConnection(SQLiteConnection sqlConnection)
        {
            sqlConnection.Close();
            sqlConnection.Dispose();
        }

        public void execute(string Expression)
        {
            SQLiteConnection connection = startConnection();
            SQLiteCommand command = new SQLiteCommand(connection);

            command.CommandText = Expression;
            command.ExecuteNonQuery();
            command.Dispose();

            killConnection(connection);
        }

        public void insert(transaction Transaction)
        {
            SQLiteConnection connection = startConnection();
            SQLiteCommand command = new SQLiteCommand(connection);

            command.CommandText = "INSERT INTO transactions (offer_number, offer_title, customer_id, price, shipping_id, date, purchase_price) VALUES ('";
            command.CommandText += Transaction.offer_number + "', '" + Transaction.offer_title + "', '" + Transaction.customer_id + "' , '";
            command.CommandText += Transaction.price + "', '" + Transaction.shipping + "', '" + Transaction.date + "', '" + Transaction.purchase_price + "')";
            command.ExecuteNonQuery();
            command.Dispose();

            killConnection(connection);
        }

        public void insert(customer Customer)
        {
            SQLiteConnection connection = startConnection();
            SQLiteCommand command = new SQLiteCommand(connection);

            command.CommandText = "INSERT INTO customers (first_name, last_name, adress, area_code, city, email, ebay_username) VALUES ('";
            command.CommandText += Customer.first_name + "', '" + Customer.last_name + "', '" + Customer.adress + "', '";
            command.CommandText += Customer.area_code + "', '" + Customer.city + "', '" + Customer.email + "', '" + Customer.ebay_username + "')";
            command.ExecuteNonQuery();
            command.Dispose();

            killConnection(connection);
        }

        public DataTable select(string expression)
        {
            DataTable datatable = new DataTable();
            SQLiteConnection connection = startConnection();
            SQLiteCommand command = new SQLiteCommand(connection);

            command.CommandText = expression;

            SQLiteDataReader reader = command.ExecuteReader();
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command.CommandText, connection);
            adapter.Fill(datatable);
            killConnection(connection);
            return datatable;
        }
    }
}
